﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class ArticleDAL : IArticleDAL
    {
        #region SQL
        public const string sqlGetArticles = "SELECT * FROM TbArticle";
        public const string sqlGetArticlesByType = "SELECT * FROM TbArticle WHERE (Type = {0})";
        public const string sqlGetArticleByID = "SELECT * FROM TbArticle WHERE (ID = {0})";
        public const string sqlGetArticlesByTitle = "SELECT * FROM TbArticle WHERE (Title = {0})";
        const string sqlUpdateArticle = "UPDATE TbArticle SET Title = {0}, ContentText = {1}, Type={2} WHERE (ID = {3})";
        const string sqlInsertArticle = "INSERT INTO TbArticle (Title, ContentText, Type) VALUES ({0},{1},{2})";
        const string sqlDeleteArticle = "Delete from TbArticle where ID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetArticles()
        {
            return DAO.Select(sqlGetArticles);
        }

        public System.Data.DataTable GetArticleByTitle(string title)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Title", title)
            };
            string sql = string.Format(sqlGetArticlesByTitle, "@Title");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetArticleByType(int type)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Type", type)
            };
            string sql = string.Format(sqlGetArticlesByType, "@Type");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetArticleByID(int id)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ID", id)
            };
            string sql = string.Format(sqlGetArticleByID, "@ID");
            return DAO.Select(sql, sp);
        }

        public bool UpdateArticle(Model.Article article)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Title", article.Title),
                new SqlParameter ("@ContentText", article.ContentText),
                new SqlParameter ("@Type", article.Type),
                new SqlParameter ("@ID", article.ID)
            };
            string sql = string.Format(sqlUpdateArticle, "@Title", "@ContentText", "@Type", "@ID");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool InsertArticle(Model.Article article)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@Title", article.Title),
                new SqlParameter ("@ContentText", article.ContentText),
                new SqlParameter ("@Type", article.Type),
            };
            string sql = string.Format(sqlInsertArticle, "@Title", "@ContentText","@Type");
            return DAO.ExecuteSql(sql, sp);
        }

        public bool DeleteArticle(int ID)
        {
            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@ID",ID),
            };
            string sql = string.Format(sqlDeleteArticle, "@ID");
            return DAO.ExecuteSql(sql, sp);
        }
    }
}
